前幾篇完成了[縣市]基本作業及[員工檔]關聯作業；接下來要做有關明細查詢的功能。
查詢有幾個要點，它沒有新增及編輯功能，需要做分頁及查詢條件功能。
1. 更新[ViewModel]：
[Model]內再新增一組[WorkSearchViewModel]。public class WorkSearchViewModel
{
[DisplayName("日期")]
[DisplayFormat(DataFormatString = "{0:yyyy/MM/dd}", ApplyFormatInEditMode = true)]
public Nullable<System.DateTime> wk_date { get; set; }
[DisplayName("客戶編號")]
public string wk_cuno { get; set; }
[DisplayName("客戶名稱")]
public string cuname { get; set; }
[DisplayName("工作類別編號")]
public string wk_type { get; set; }
[DisplayName("工作類別名稱")]
public string wktypename { get; set; }
[DisplayName("價格")]
public Nullable<decimal> wk_price { get; set; }
[DisplayName("員工編號")]
public string wk_emno { get; set; }
[DisplayName("員工姓名")]
public string emname { get; set; }
}
2. 新增[WorkSearchController]：
[Controllers] --> [加入] --> [控制器]。空白控制器，等未來熟悉之後，再讓工具幫您產出有內建程式的控制器吧！Index：就是功能首頁，來調整一下讓其可以秀出表格資料。[WorkSearchController]新增[Index]程式碼，函式需增加幾組參數，分別是sortBy(排序)、searchCuName(客戶名稱)、searchTypeName(工作類別)、searchEmName(員工名稱)、page(分頁)等。public ActionResult Index(string sortBy, string searchCuName, string searchTypeName, string searchEmName, int? page)
{
Models.HairSystemEntities db = new Models.HairSystemEntities();
var result = (from wk in db.workorder
join cu in db.customer on wk.wk_cuno equals cu.cu_no
join em in db.employee on wk.wk_emno equals em.em_no
join wt in db.worktype on wk.wk_type equals wt.no
select new WorkSearchViewModel
{
wk_date = wk.wk_date,
wk_cuno = wk.wk_cuno,
cuname = cu.cu_name,
wk_type = wk.wk_type,
wktypename = wt.name,
wk_price = wk.wk_price,
wk_emno = wk.wk_emno,
emname = em.em_name
});
//工作類別改下拉
var TypeNameLst = new List<SelectListItem>();
foreach (var item in db.worktype)
{
TypeNameLst.Add(new SelectListItem { Text = item.name, Value = item.name, Selected = item.name.Equals(searchTypeName) });
}
//將三個條件分別傳至前端，待分頁改變時，可重新給條件
ViewBag.searchTypeName = TypeNameLst;
ViewBag.SelectedType = searchTypeName; //分頁指定用的下拉條件
ViewBag.searchCuName = searchCuName;
ViewBag.searchEmName = searchEmName;
//查詢條件
//客戶名稱
if (!string.IsNullOrEmpty(searchCuName))
{
//一般元件為模糊比對
result = result.Where(s => s.cuname.Contains(searchCuName));
}
//工作類別名稱
if (!string.IsNullOrEmpty(searchTypeName))
{
//下拉元件改為完全符合
result = result.Where(s => s.wktypename == searchTypeName);
}
//員工姓名
if (!string.IsNullOrEmpty(searchEmName))
{
//一般元件為模糊比對
result = result.Where(s => s.emname.Contains(searchEmName));
}
//排序功能
//第一次為客戶名稱降冪
ViewBag.CustNameSortParm = string.IsNullOrEmpty(sortBy) ? "cuname" : "";
//第一次為員工姓名升冪
ViewBag.EmplNameSortParm = sortBy == "emname" ? "emname_desc" : "emname";
//第一次為工作類別名稱升冪
ViewBag.WktypenameSortParm = sortBy == "wktypename" ? "wktypename_desc" : "wktypename";
//第一次為日期升冪
ViewBag.WkDateSortParm = sortBy == "wkdate" ? "wkdate_desc" : "wkdate";
switch (sortBy)
{
case "cuname":
result = result.OrderBy(s => s.cuname);
break;
case "emname_desc":
result = result.OrderByDescending(s => s.emname);
break;
case "emname":
result = result.OrderBy(s => s.emname);
break;
case "wktypename_desc":
result = result.OrderByDescending(s => s.wktypename);
break;
case "wktypename":
result = result.OrderBy(s => s.wktypename);
break;
case "wkdate_desc":
result = result.OrderByDescending(s => s.wk_date);
break;
case "wkdate":
result = result.OrderBy(s => s.wk_date);
break;
default:
result = result.OrderByDescending(s => s.cuname);
break;
}
//分頁排序
ViewBag.CurrentSort = sortBy;
int pageSize = 5; //每頁筆數
int pageNumber = page ?? 1; //起始頁數
return View(result.ToPagedList(pageNumber, pageSize));
}
安裝[PagedList]及[PagedList.Mvc]套件，[工具] --> [NuGet套件管理員] --> [管理方案的NuGet套件]。
安裝後，記得關閉方案再重新開啟專案，並加上引用。
新增檢視頁面：在[View]上按右鍵 --> [新增檢視]。
預設選項，再按下[加入]。
填入相關資料，範本選擇List(清單)，模型及類別選擇建立的Models。
完成的檢視畫面，稍為調整一下，調它看起來專業一點。
因為在[WorkSearchController]有加入分頁套件，所以載入的model要調整一下。
@*@model IEnumerable<Hair.Models.workorder>*@
@using PagedList
@using PagedList.Mvc
@model IPagedList<Hair.Models.WorkSearchViewModel>
@{
ViewBag.Title = "明細清單";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>明細首頁</h2>
@using (Html.BeginForm("Index", "WorkSearch", FormMethod.Get))
{
<p>
客戶名稱：@Html.TextBox("searchCuName", "", new { value = ViewBag.searchCuName })
工作類別名稱：@Html.DropDownList("searchTypeName", (List<SelectListItem>)ViewBag.searchTypeName, "ALL")
員工姓名：@Html.TextBox("searchEmName", "", new { value = ViewBag.searchEmName })
<input type="submit" value="查詢" />
</p>
}
<table class="table" border="1">
<tr>
@*searchTypeName一定要選[所選下拉條件]*@
<th style="text-align: center">
@Html.ActionLink("日期排序", "Index", new { sortBy = ViewBag.WkDateSortParm, searchCuName = ViewBag.searchCuName, searchTypeName = ViewBag.SelectedType, searchEmName = ViewBag.searchEmName })
</th>
<th></th>
<th style="text-align: center">
@Html.ActionLink("客戶名稱排序", "Index", new { sortBy = ViewBag.CustNameSortParm, searchCuName = ViewBag.searchCuName, searchTypeName = ViewBag.SelectedType, searchEmName = ViewBag.searchEmName })
</th>
<th></th>
<th style="text-align: center">
@Html.ActionLink("工作類別名稱排序", "Index", new { sortBy = ViewBag.WktypenameSortParm, searchCuName = ViewBag.searchCuName, searchTypeName = ViewBag.SelectedType, searchEmName = ViewBag.searchEmName })
</th>
<th></th>
<th></th>
<th style="text-align: center">
@Html.ActionLink("員工姓名排序", "Index", new { sortBy = ViewBag.EmplNameSortParm, searchCuName = ViewBag.searchCuName, searchTypeName = ViewBag.SelectedType, searchEmName = ViewBag.searchEmName })
</th>
</tr>
<tr>
<th style="text-align: center">
@Html.DisplayNameFor(Model => Model.FirstOrDefault().wk_date)
</th>
<th style="text-align: center">
@Html.DisplayNameFor(Model => Model.FirstOrDefault().wk_cuno)
</th>
<th style="text-align: center">
@Html.DisplayNameFor(Model => Model.FirstOrDefault().cuname)
</th>
<th style="text-align: center">
@Html.DisplayNameFor(Model => Model.FirstOrDefault().wk_type)
</th>
<th style="text-align: center">
@Html.DisplayNameFor(Model => Model.FirstOrDefault().wktypename)
</th>
<th style="text-align: center">
@Html.DisplayNameFor(Model => Model.FirstOrDefault().wk_price)
</th>
<th style="text-align: center">
@Html.DisplayNameFor(Model => Model.FirstOrDefault().wk_emno)
</th>
<th style="text-align: center">
@Html.DisplayNameFor(Model => Model.FirstOrDefault().emname)
</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.wk_date)
</td>
<td>
@Html.DisplayFor(modelItem => item.wk_cuno)
</td>
<td>
@Html.DisplayFor(modelItem => item.cuname)
</td>
<td>
@Html.DisplayFor(modelItem => item.wk_type)
</td>
<td>
@Html.DisplayFor(modelItem => item.wktypename)
</td>
<td style="text-align: right">
@Html.DisplayFor(modelItem => item.wk_price)
</td>
<td>
@Html.DisplayFor(modelItem => item.wk_emno)
</td>
<td>
@Html.DisplayFor(modelItem => item.emname)
</td>
</tr>
}
</table>
Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount
@*searchTypeName要帶入所選下拉條件*@
@Html.PagedListPager(Model, page => Url.Action("Index", new { page, sortBy = ViewBag.CurrentSort, searchCuName = ViewBag.searchCuName, searchEmName = ViewBag.searchEmName, searchTypeName = ViewBag.SelectedType }),
PagedListRenderOptions.ClassicPlusFirstAndLast)
這樣就完成了~~
基本上就包含了常用的查詢條件、分頁等功能，熟悉後再往進階去發展吧！
iThome鐵人賽
看影片追技術